﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace GC_School_of_Careers
{
    public partial class uctransaction : UserControl
    {
        private int iRowIndex = 0;

        public uctransaction()
        {
            InitializeComponent();


                DataGridViewCheckBoxColumn chkstudent = new DataGridViewCheckBoxColumn();
                dgcstudent.Columns.Add(chkstudent);
                chkstudent.HeaderText = "Select";
                chkstudent.Name = "chkstudent";

            DataGridViewCheckBoxColumn chksubject = new DataGridViewCheckBoxColumn();
            dgtrnsubject.Columns.Add(chksubject);
            chksubject.HeaderText = "Select";
            chksubject.Name = "chksubject";

            DataGridViewCheckBoxColumn chkunit = new DataGridViewCheckBoxColumn();
            dgtrnunit.Columns.Add(chkunit);
            chkunit.HeaderText = "Select";
            chkunit.Name = "chkunit";

            this.cUSTOMERTableAdapter.Fill(this.dtstudent.CUSTOMER);
            this.sUPPLIERTableAdapter.Fill(this.dtexaminationcenter.SUPPLIER);
            this.tALSUBJECTTableAdapter.Fill(this.dssubject.TALSUBJECT);
            this.tALUNITTableAdapter.Fill(this.dsunit.TALUNIT);
            this.tALTRNHEADERTableAdapter.Fill(this.dsheader.TALTRNHEADER);


            this.cbyear.SelectedIndex = (DateTime.Today.Year) - 2011;
        }

        private void cbyear_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void txtstudentcode_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dtstudent.Tables[0];
            dv.RowFilter = "CODE Like " + "'%" + txtstudentcode.Text + "%'"; // +txtSearchString.Text;
            dgcstudent.DataSource = dv;
        }

        private void btntrnapply_Click(object sender, EventArgs e)
        {
            int v_record_exists = 0;
            Int32 NoOfStudentsSelected = 0;
            String vErrorMessage = null;

            int v_acyear = 2011 + cbyear.SelectedIndex;
            DateTime v_docdate = dtptrndocdate.Value.Date; //"01/01/2001";
            string s_docdate = v_docdate.ToString("ddMMyyyy"); //yyyy-MM-dd HH:mm tt
            string v_studentcode = "000000";
            int header_exists = 0;
            
            foreach (DataGridViewRow obRow in dgcstudent.Rows)
            {
                DataGridViewCheckBoxCell chk = obRow.Cells[4] as DataGridViewCheckBoxCell;
                if (Convert.ToBoolean(chk.Value) == true) { NoOfStudentsSelected += 1; }
            }

            if (dtptrndocdate.Value == null) { vErrorMessage = vErrorMessage + "Date - "; }
            if (cbyear.SelectedText == null) { vErrorMessage = vErrorMessage + "Academic Year - "; }
            if (NoOfStudentsSelected != 1) { vErrorMessage = vErrorMessage + "Invalid Number of Students - "; }
            if (vErrorMessage != null) { vErrorMessage = vErrorMessage + "Error"; }

            if (vErrorMessage != null) { MessageBox.Show(vErrorMessage); }
            else
            {
                System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection("Data Source=ATLANTIS;Persist Security Info=True;Password=GCS;User ID=GCS");
                conn.Open();
                string ORASQLTRNHEADER = "";
                string ORASQLTRNLINE = "";

                for (int i = 0; i < (this.dgcstudent.Rows.Count - 1); ++i)
                {
                    if (Convert.ToBoolean(dgcstudent.Rows[i].Cells[4].Value) == true)
                    {

                        v_studentcode = dgcstudent.Rows[i].Cells[0].Value.ToString();


                        for (int z = 0; z < (this.dgtrnheader.Rows.Count - 1); ++z)
                        {
                            string check_studentcode = dgtrnheader.Rows[z].Cells[2].Value.ToString();
                            string check_acyear = dgtrnheader.Rows[z].Cells[3].Value.ToString();
                            string check_v_acyear = v_acyear.ToString();

//                            MessageBox.Show(check_acyear.ToString());
                            if (v_studentcode == check_studentcode)
                            {
//                            MessageBox.Show(check_studentcode);
                            if (check_v_acyear == check_acyear)
                                {
//                                    MessageBox.Show(check_acyear.ToString());
                                    header_exists = 1;
                                }
                            }
                        }

//                                            + ",CONCAT(CONCAT('EX','" + v_acyear + "'),(select NVL(MAX(ID),0)+1 from TALTRNHEADER))"
                        ORASQLTRNHEADER = "INSERT INTO TALTRNHEADER VALUES ((select NVL(MAX(ID),0)+1 from TALTRNHEADER)"
                                            + ",CONCAT(CONCAT('EX','" + v_acyear + "'),LPAD((select NVL(MAX(ID),0)+1 from TALTRNHEADER),4,'0'))"
                                            + ",'" + v_studentcode + "'"
                                            + "," + v_acyear
                                            + ",'" + s_docdate + "'"
                                            + ",0"
                                            + ",0"
                                            + ",0"
                                            + ",0"
                                            + ",0"
                                            + ",'" + GlobalUser.GlobalVar + "'"
                                            + ")";
                        
                        System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(ORASQLTRNHEADER);
                        command.Connection = conn;
                        if (header_exists < 1){
                            command.ExecuteNonQuery();
                        }

                    }
                }



                for (int i = 0; i < (this.dgtrnunit.Rows.Count - 1); ++i)
                {
                    if (Convert.ToBoolean(dgtrnunit.Rows[i].Cells[6].Value) == true)
                    {
                        string v_unitcode = dgtrnunit.Rows[i].Cells[0].Value.ToString();
                        ORASQLTRNLINE = "INSERT INTO TALTRNLINE VALUES ((select NVL(MAX(ID),0)+1 from TALTRNLINE)" //,0,0,0,0,0,0,0,0,0,0,0";
                                        + ",'" + v_unitcode + "'" // + "(SELECT SUBJECTCODE FROM TALUNIT WHERE CODE = '" + v_unitcode + "')" + "(SELECT CENTERCODE FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",'" + v_unitcode + "'"
                                        + ",(SELECT DESCR FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT PRICE FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT COST FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT VAT FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT SUBJECTCODE FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT SUBJECTDESCR FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT CENTERCODE FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT CENTERNAME FROM TALUNIT WHERE CODE = '" + v_unitcode + "')"
                                        + ",(SELECT CODE FROM TALTRNHEADER WHERE STUDENTCODE = '" + v_studentcode + "' AND ACYEAR = " + v_acyear + ")"
                                        + ",'" + v_studentcode + "'"
                                        + "," + v_acyear
                                        + ",'" + GlobalUser.GlobalVar + "'"
                                        + ",0)";
                        System.Data.OracleClient.OracleCommand command1 = new System.Data.OracleClient.OracleCommand(ORASQLTRNLINE);
                        command1.Connection = conn;
                        try
                        {
                            command1.ExecuteNonQuery();
                        }
                        catch
                        {
                            MessageBox.Show("Record Exists");
                        }
                    }
                }
                this.tALTRNLINETableAdapter.Fill(this.dsline.TALTRNLINE);
                DataView dv1 = new DataView();
                dv1.Table = this.dsline.Tables[0];
                dv1.RowFilter = "STUDENTCODE Like " + "'%" + v_studentcode + "%' AND ACYEAR =" + v_acyear + " AND USERNAME Like '%" + GlobalUser.GlobalVar + "%'";
                dgtrnline.DataSource = dv1;

                this.tALTRNHEADERTableAdapter.Fill(this.dsheader.TALTRNHEADER);
 
                conn.Close();
            }



        }

        private void txtstudentname_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dtstudent.Tables[0];
            dv.RowFilter = "NAME Like " + "'%" + txtstudentname.Text + "%'"; // +txtSearchString.Text;
            dgcstudent.DataSource = dv;
        }

        private void txtstudentformcode_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dtstudent.Tables[0];
            dv.RowFilter = "FORMCODE Like " + "'%" + txtstudentformcode.Text + "%'"; // +txtSearchString.Text;
            dgcstudent.DataSource = dv;
        }

        private void txtstudentformname_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dtstudent.Tables[0];
            dv.RowFilter = "FORMDESCRIPTION Like " + "'%" + txtstudentformname.Text + "%'"; // +txtSearchString.Text;
            dgcstudent.DataSource = dv;
        }

        private void btntrnclear_Click(object sender, EventArgs e)
        {

            foreach (DataGridViewRow obRow in dgtrnunit.Rows)
            {
                DataGridViewCheckBoxCell chk = obRow.Cells[5] as DataGridViewCheckBoxCell;
                chk.Value = false;
            }

            DataView dv1 = new DataView();
            dv1.Table = this.dsunit.Tables[0];
            dgtrnunit.DataSource = dv1;

        }

        private void dgcstudent_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
/*            this.dgcstudent.Focus();

            MessageBox.Show("Gotcha");

            string v_studentcode = "000000";
            int v_acyear = 2011 + cbyear.SelectedIndex;*/

//            for (int i = 0; i < (this.dgcstudent.Rows.Count - 1); ++i)
//            {
//                if (Convert.ToBoolean(dgcstudent.Rows[i].Cells[4].Value) == true)
//                {
//                    v_studentcode = dgcstudent.Rows[i].Cells[0].Value.ToString();
//                    MessageBox.Show("Found Check Box");
//                }
//            }

//            foreach (DataGridViewRow obRow in dgcstudent.Rows)
//            {
//                DataGridViewCheckBoxCell chk = obRow.Cells[4] as DataGridViewCheckBoxCell;
//                DataGridViewTextBoxCell student = obRow.Cells[0] as DataGridViewTextBoxCell;
//                if (Convert.ToBoolean(chk.Value) == true) { MessageBox.Show("True"); };//{ v_studentcode = student.Value.ToString(); }
//            }

/*            if (v_studentcode != "000000")
            {
                this.tALTRNLINETableAdapter.Fill(this.dsline.TALTRNLINE);
                DataView dv1 = new DataView();
                dv1.Table = this.dsline.Tables[0];
                dv1.RowFilter = "STUDENTCODE Like " + "'%" + v_studentcode + "%' AND ACYEAR =" + v_acyear + " AND USERNAME Like '%" + GlobalUser.GlobalVar + "%'";
                dgtrnline.DataSource = dv1;
            }*/

        }

        private void btnheaderload_Click(object sender, EventArgs e)
        {
            string v_studentcode = "000000";
            Int32 NoOfStudentsSelected = 0;
            String vErrorMessage = null;
            int v_acyear = 2011 + cbyear.SelectedIndex;
            foreach (DataGridViewRow obRow in dgcstudent.Rows)
            {
                DataGridViewCheckBoxCell chk = obRow.Cells[4] as DataGridViewCheckBoxCell;
                if (Convert.ToBoolean(chk.Value) == true) { NoOfStudentsSelected += 1; }
            }

            if (dtptrndocdate.Value == null) { vErrorMessage = vErrorMessage + "Date - "; }
            if (cbyear.SelectedText == null) { vErrorMessage = vErrorMessage + "Academic Year - "; }
            if (NoOfStudentsSelected != 1) { vErrorMessage = vErrorMessage + "Invalid Number of Students - "; }
            if (vErrorMessage != null) { vErrorMessage = vErrorMessage + "Error"; }

            if (vErrorMessage != null) { MessageBox.Show(vErrorMessage); }
            else
            {
                for (int i = 0; i < (this.dgcstudent.Rows.Count - 1); ++i)
                {
                    if (Convert.ToBoolean(dgcstudent.Rows[i].Cells[4].Value) == true)
                    {
                        v_studentcode = dgcstudent.Rows[i].Cells[0].Value.ToString();
                    }
                }
            }
            this.tALTRNLINETableAdapter.Fill(this.dsline.TALTRNLINE);
            DataView dv1 = new DataView();
            dv1.Table = this.dsline.Tables[0];
            dv1.RowFilter = "STUDENTCODE Like " + "'%" + v_studentcode + "%' AND ACYEAR =" + v_acyear + " AND USERNAME Like '%" + GlobalUser.GlobalVar + "%' AND POSTED = 0";
            dgtrnline.DataSource = dv1;


        }

        private void btntrnentrydelete_Click(object sender, EventArgs e)
        {
            int v_acyear = 2011 + cbyear.SelectedIndex;
            string v_studentcode = "000000";
            for (int i = 0; i < (this.dgcstudent.Rows.Count - 1); ++i)
            {
                if (Convert.ToBoolean(dgcstudent.Rows[i].Cells[4].Value) == true)
                {
                    v_studentcode = dgcstudent.Rows[i].Cells[0].Value.ToString();
                }
            }

            string index = dgtrnline.SelectedCells[0].Value.ToString(); //.RowIndex;
            decimal decimalVal = System.Convert.ToDecimal(index);
//            MessageBox.Show(index);
            DialogResult dr=MessageBox.Show("Are you sure you want to delete this row ? ", "Confirm deleting", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes) //if 
            {
                try
                {
                    this.tALTRNLINETableAdapter.Delete(decimalVal);
                    MessageBox.Show("Deleted");
                    this.tALTRNLINETableAdapter.Fill(this.dsline.TALTRNLINE);
                    DataView dv1 = new DataView();
                    dv1.Table = this.dsline.Tables[0];
                    dv1.RowFilter = "STUDENTCODE Like " + "'%" + v_studentcode + "%' AND ACYEAR =" + v_acyear + " AND USERNAME Like '%" + GlobalUser.GlobalVar + "%'";
                    dgtrnline.DataSource = dv1; 

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Delete failed");
                }
            }

        }

        private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {

        }

        private void dgtrncenter_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dgtrnline_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void bindingNavigatorDeleteItem_Click_1(object sender, EventArgs e)
        {

        }

        private void bindingNavigatorDeleteItem_Click_2(object sender, EventArgs e)
        {

        }

        private void bindingNavigatorDeleteItem_Click_3(object sender, EventArgs e)
        {

        }

        private void bindingNavigatorDeleteItem_Click_4(object sender, EventArgs e)
        {

        }

        private void gbtrnentry_Enter(object sender, EventArgs e)
        {

        }

        private void btntrnpost_Click(object sender, EventArgs e)
        {

            //CHECK DATE
            int v_acyear = 2011 + cbyear.SelectedIndex;
            DateTime v_docdate = dtptrndocdate.Value.Date; //"01/01/2001";
            string s_docdate = v_docdate.ToString("ddMMyyyy"); //yyyy-MM-dd HH:mm tt
            string v_studentcode = "000000";


            for (int i = 0; i < (this.dgcstudent.Rows.Count - 1); ++i)
            {
                if (Convert.ToBoolean(dgcstudent.Rows[i].Cells[4].Value) == true)
                {
                    v_studentcode = dgcstudent.Rows[i].Cells[0].Value.ToString();
                }
            }


            
            string ORASQLTRNLINEPOST = "";
            System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection("Data Source=ATLANTIS;Persist Security Info=True;Password=GCS;User ID=GCS");
            conn.Open();


            for (int i = 0; i < (this.dgtrnline.Rows.Count - 1); ++i)
            {
                String lineid = dgtrnline.Rows[i].Cells[0].Value.ToString();
                ORASQLTRNLINEPOST = "UPDATE TALTRNLINE SET POSTED = 1 WHERE ID = " + lineid;
                System.Data.OracleClient.OracleCommand command1 = new System.Data.OracleClient.OracleCommand(ORASQLTRNLINEPOST);
                command1.Connection = conn;
                command1.ExecuteNonQuery();
             }


            ORASQLTRNLINEPOST = "UPDATE TALTRNHEADER SET DOCPOSTED = 1 WHERE CODE IN (SELECT TRNCODE FROM TALTRNLINE WHERE POSTED = 1)";
            System.Data.OracleClient.OracleCommand command2 = new System.Data.OracleClient.OracleCommand(ORASQLTRNLINEPOST);
            command2.Connection = conn;
            command2.ExecuteNonQuery();



            conn.Close();
            MessageBox.Show("Posted Succesfully");





            this.Dispose(false);
        }

        private void dgtrnline_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
        {

        }


        private void dgtrnpay_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dssubject.Tables[0];
            dv.RowFilter = "CODE Like " + "'%" + txtsubjectsubjectcode.Text + "%' OR DESCR Like " + "'%" + txtsubjectsubjectcode.Text + "%'"; // +txtSearchString.Text;
            dgtrnsubject.DataSource = dv;
        }

        private void txtunitsubjectcode_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dsunit.Tables[0];
            dv.RowFilter = "SUBJECTCODE Like " + "'%" + txtunitsubjectcode.Text + "%' OR SUBJECTDESCR Like " + "'%" + txtunitsubjectcode.Text + "%'"; // +txtSearchString.Text;
            dgtrnunit.DataSource = dv;

        }

        private void txtunitcentercode_TextChanged(object sender, EventArgs e)
        {
            DataView dv = new DataView();
            dv.Table = this.dsunit.Tables[0];
            dv.RowFilter = "CENTERCODE Like " + "'%" + txtunitcentercode.Text + "%' OR CENTERNAME Like " + "'%" + txtunitcentercode.Text + "%'"; // +txtSearchString.Text;
            dgtrnunit.DataSource = dv;

        }


    }
}
